/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package app_99;

import java.sql.*;

/**
 * Class for communicating with the mvpdata table
 * 
 * @author
 */
public class DAO {

    private static Connection conn = null;
    /////////////////////////////////////////////
    ///   Singleton pattern
    /**
     * Singleton instance of the object
     */
    private static DAO instance = null;

    /** Creates a new instance of PhotoSetDAO */
    private DAO() {
    }

    /**
     * Returns a singleton instance of the DAO
     * @return singleton instance of the DAO
     */
    public static synchronized DAO getInstance() {
        if (instance == null) {
            instance = new DAO();
        }
        return instance;
    }

    public synchronized void saveSession(Session sess) throws DataException {
        try {
            conn = ConnectionPool.getInstance().get();

            if (sess.isAlreadyInDB()) {
                updateSession(sess, conn);
            } else {
                insertSession(sess, conn);
            }

            conn.commit();
            ConnectionPool.getInstance().release(conn);
        } catch (ConnectionPoolException e) {
            throw new DataException("Could not get a connection to the database.", e);
        } catch (SQLException e) {
            try {
                conn.rollback();
                ConnectionPool.getInstance().release(conn);
            } catch (SQLException e2) {
                throw new DataException("Big error: could not roll back the transaction", e2);
            } catch (ConnectionPoolException e3) {
                throw new DataException("Big error: could not even release the connection", e3);
            }
            throw new DataException("Could not save record", e);
        }
    }

    private synchronized void updateSession(Session sess, Connection conn) throws SQLException {
        PreparedStatement pstmt = conn.prepareStatement("UPDATE mvpdata " +
                "SET TotalVisitors = ?, " +
                "PeekVisitors = ?, " +
                "AvgVisitors = ?, " +
                "Income = ?, " +
                "AvgPerPatron = ? " +
                "WHERE establishment = ? AND RepDate = ?");
        pstmt.setFloat(1, sess.getTotal());
        pstmt.setFloat(2, sess.getPeek());
        pstmt.setDouble(3, sess.getAverage());
        pstmt.setDouble(4, sess.getRevenue());
        pstmt.setDouble(5, sess.getAvgRev());
        pstmt.setString(6, sess.getEstablishment());
        pstmt.setDate(7, sess.getDate());
        pstmt.execute();
        pstmt.close();

    }

    private synchronized void insertSession(Session sess, Connection conn) throws SQLException {
        // do the insert SQL statement
        PreparedStatement pstmt = conn.prepareStatement("INSERT INTO mvpdata " +
                "(RepDate, Establishment, TotalVisitors, PeekVisitors, AvgVisitors, Income, AvgPerPatron) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?)");
        pstmt.setDate(1, sess.getDate());
        pstmt.setString(2, sess.getEstablishment());
        pstmt.setFloat(3, sess.getTotal());
        pstmt.setFloat(4, sess.getPeek());
        pstmt.setDouble(5, sess.getAverage());
        pstmt.setDouble(6, sess.getRevenue());
        pstmt.setDouble(7, sess.getAvgRev());

        pstmt.executeUpdate();
        pstmt.close();

        // tell the object that it's now in the db (so we call update next time not insert)
        sess.setAlreadyInDB(true);
    }

    //If it already exists, then it loads in the data. If not, then it creates a new session
    public synchronized Session readSession(String est, java.sql.Date date) {

        Session sess = null;

        try {
            // retrieve a database connection from the pool
            conn = ConnectionPool.getInstance().get();
            // create a prepared statement and run the query
            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM mvpdata " +
                    "WHERE establishment = ? AND RepDate = ?");
            pstmt.setString(1, est);
            pstmt.setDate(2, date);
            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {
                // create and populate the object in memory
                sess = new Session();
                sess.setEstablishment(est);
                sess.setDate(date);
                sess.setTotal(rs.getInt("TotalVisitors"));
                sess.setPeek(rs.getInt("PeekVisitors"));
                sess.setAvgRev(rs.getDouble("AvgVisitors"));
                sess.setRevenue(rs.getDouble("Income"));
                sess.setAvgRev(rs.getDouble("AvgPerPatron"));
                sess.setAlreadyInDB(true);

            } else {
                java.util.Date now = new java.util.Date();
                java.sql.Date mySqlDate = new java.sql.Date(now.getTime());
                //java.text.SimpleDateFormat dateFormatter = new java.text.SimpleDateFormat("MM/dd/yyyy");
                //String theFormattedDate = dateFormatter.format(now);

                sess = new Session();
                sess.setEstablishment(est);
                sess.setDate(mySqlDate);
                try {
                    ConnectionPool.getInstance().release(conn);
                } catch (ConnectionPoolException ex) {
                    throw new DataException("Big error: could not even release the connection", ex);
                }

            }
            pstmt.close();
            rs.close();

            try {
                ConnectionPool.getInstance().release(conn);
            } catch (ConnectionPoolException ex) {
                throw new DataException("Big error: could not even release the connection", ex);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        // return the BO
        return sess;

    }
    }
